﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;

namespace CrmApplication.Models
{
	public class CampaignModels
	{
		#region RegionMappingCampaign

		public decimal Id{ get; set; }
        public string Name { get; set; }

		public decimal NrContact { get; set; }
		public decimal IdContact { get; set; }

		#endregion

		#region RegionOracleConnect

		private static OracleConnection connect;

		static CampaignModels ()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["HomeOrcl"].ConnectionString;
            connect = new OracleConnection(connString);
        }

		#endregion

		#region RegionCampaigOperations
		/// <summary>
		/// select all campaigns
		/// </summary>
		/// <param name="searchCampaign"></param>
		/// <returns></returns>
		public static List<CampaignModels> GetCampaign (string searchCampaign)
		{
			string selectCampaign = "where campaign_name like '" + searchCampaign + "%'";
			string selectString = "select id_campaign,campaign_name from campaign "
									+ selectCampaign
									+ " order by campaign_name ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			List<CampaignModels> result = new List<CampaignModels>();
			CampaignModels result1 = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new CampaignModels();
						result1.Id = sdr.GetDecimal(0);
						result1.Name = sdr.GetString(1);
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// select all campaigns and number of contacts for each campaign
		/// </summary>
		/// <returns></returns>
		public static List<CampaignModels> GetCampaignCount ()
		{			
			string selectString =" select c.id_campaign,c.campaign_name ,count(cp.id_campaign) "
									+ " from campaign c "
									+ " left join campaignperson  cp on c.id_campaign=cp.id_campaign "
									+ " group by c.id_campaign,c.campaign_name"
									+ " order  by c.campaign_name ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			List<CampaignModels> result = new List<CampaignModels>();
			CampaignModels result1 = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new CampaignModels();
						result1.Id = sdr.GetDecimal(0);
						result1.Name = sdr.GetString(1);
						result1.NrContact = sdr.GetDecimal(2);
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// insert new campaign into db
		/// </summary>
		/// <param name="campaignName"></param>
		public static void AddCampaign (string campaignName)
		{
			string insert = "INSERT INTO campaign(campaign_name) VALUES (:campaignName)";

			OracleCommand usercomm = new OracleCommand(insert);

			usercomm.Parameters.Add(":campaignName", campaignName);

			usercomm.Connection = connect;
			try
			{
				connect.Open();
				usercomm.ExecuteNonQuery();
			}
			finally
			{
				connect.Close();
			}
		}

		/// <summary>
		/// add/move a contact into a campaign
		/// </summary>
		/// <param name="campaign"></param>
		public static void MoveContactCampaign (CampaignModels campaign)
		{
			string insert = "insert into campaignperson(id_campaign,id_person) values (:idCampaign,:idPerson)";

			OracleCommand usercomm = new OracleCommand(insert);

			usercomm.Parameters.Add(":idCampaign", campaign.Id);
			usercomm.Parameters.Add(":idPerson", campaign.IdContact);

			usercomm.Connection = connect;
			try
			{
				connect.Open();
				usercomm.ExecuteNonQuery();
			}
			finally
			{
				connect.Close();
			}
		}

		/// <summary>
		/// check if contact already exists into a campaign
		/// </summary>
		/// <param name="campaign"></param>
		/// <returns></returns>
		public static CampaignModels CheckContactCampaign (CampaignModels campaign)
		{
			string selectString = "select id_campaign,id_person from campaignperson "
									+" where id_campaign=:idCampaign and id_person=:idPerson ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idCampaign", campaign.Id);
			loginComm.Parameters.Add(":idPerson", campaign.IdContact);

			CampaignModels result = null;

			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result = new CampaignModels();
						result.Id = sdr.GetDecimal(0);
						result.IdContact = sdr.GetDecimal(1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// delete a contact from a campaign
		/// </summary>
		/// <param name="campaign"></param>
		public static void DeleteContactCampaign (CampaignModels campaign)
		{
			string insert = "delete from campaignperson "
							+ "where id_campaign=:idCampaign and id_person=:idPerson";

			OracleCommand usercomm = new OracleCommand(insert);

			usercomm.Parameters.Add(":idCampaign", campaign.Id);
			usercomm.Parameters.Add(":idPerson", campaign.IdContact);

			usercomm.Connection = connect;
			try
			{
				connect.Open();
				usercomm.ExecuteNonQuery();
			}
			finally
			{
				connect.Close();
			}
		}
		#endregion


		#region RegionJsonArraysCampaign
		/// <summary>
		/// create json array for getcampaign
		/// </summary>
		/// <param name="searchCampaign"></param>
		/// <returns></returns>
		public static CampaignContainer GetCampaignArray (string searchCampaign)
		{
			CampaignModels[] data = new CampaignModels[20];
			data = CampaignModels.GetCampaign(searchCampaign).ToArray();

			CampaignContainer container = new CampaignContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		/// <summary>
		/// create json array for getcampaigncount
		/// </summary>
		/// <returns></returns>
		public static CampaignContainer GetCampaignCountArray ()
		{
			CampaignModels[] data = new CampaignModels[20];
			data = CampaignModels.GetCampaignCount().ToArray();

			CampaignContainer container = new CampaignContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}
		#endregion
	}

	public class CampaignContainer
	{
		public CampaignModels[] Records { get; set; }
		public int Count { get; set; }
	}
}